<!DOCTYPE html>



  


<html class="theme-next gemini use-motion" lang="zh-Hans">
<head>
  <meta charset="UTF-8"/>
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1"/>
<script src="//cdn.bootcss.com/pace/1.0.2/pace.min.js"></script>
<link href="//cdn.bootcss.com/pace/1.0.2/themes/pink/pace-theme-flash.css" rel="stylesheet">
<meta name="theme-color" content="#222">









<meta http-equiv="Cache-Control" content="no-transform" />
<meta http-equiv="Cache-Control" content="no-siteapp" />
















  
  
  <link href="/lib/fancybox/source/jquery.fancybox.css?v=2.1.5" rel="stylesheet" type="text/css" />







<link href="/lib/font-awesome/css/font-awesome.min.css?v=4.6.2" rel="stylesheet" type="text/css" />

<link href="/css/main.css?v=5.1.4" rel="stylesheet" type="text/css" />


  <link rel="apple-touch-icon" sizes="180x180" href="/images/apple-touch-icon-next.png?v=5.1.4">


  <link rel="icon" type="image/png" sizes="32x32" href="/images/midium.png?v=5.1.4">


  <link rel="icon" type="image/png" sizes="16x16" href="/images/small.png?v=5.1.4">


  <link rel="mask-icon" href="/images/logo.svg?v=5.1.4" color="#222">





  <meta name="keywords" content="数据库,SQL," />




  


  <link rel="alternate" href="/atom.xml" title="磊迹" type="application/atom+xml" />






<meta name="description" content="浅谈数据库">
<meta property="og:type" content="article">
<meta property="og:title" content="数据库——SQL">
<meta property="og:url" content="http://yoursite.com/%E6%95%B0%E6%8D%AE%E5%BA%93/%E6%95%B0%E6%8D%AE%E5%BA%93-SQL/index.html">
<meta property="og:site_name" content="磊迹">
<meta property="og:description" content="浅谈数据库">
<meta property="article:published_time" content="2019-10-24T16:00:00.000Z">
<meta property="article:modified_time" content="2023-08-20T07:59:22.338Z">
<meta property="article:author" content="Lei Zhou">
<meta property="article:tag" content="数据库">
<meta property="article:tag" content="SQL">
<meta name="twitter:card" content="summary">



<script type="text/javascript" id="hexo.configurations">
  var NexT = window.NexT || {};
  var CONFIG = {
    root: '/',
    scheme: 'Gemini',
    version: '5.1.4',
    sidebar: {"position":"left","display":"post","offset":12,"b2t":true,"scrollpercent":true,"onmobile":false},
    fancybox: true,
    tabs: true,
    motion: {"enable":true,"async":false,"transition":{"post_block":"fadeIn","post_header":"slideDownIn","post_body":"slideDownIn","coll_header":"slideLeftIn","sidebar":"slideUpIn"}},
    duoshuo: {
      userId: '0',
      author: '博主'
    },
    algolia: {
      applicationID: '',
      apiKey: '',
      indexName: '',
      hits: {"per_page":10},
      labels: {"input_placeholder":"Search for Posts","hits_empty":"We didn't find any results for the search: ${query}","hits_stats":"${hits} results found in ${time} ms"}
    }
  };
</script>



  <link rel="canonical" href="http://yoursite.com/数据库/数据库-SQL/"/>





  <title>数据库——SQL | 磊迹</title>
  








<meta name="generator" content="Hexo 4.2.0"></head>

<body itemscope itemtype="http://schema.org/WebPage" lang="zh-Hans">

  
  
    
  

  <div class="container sidebar-position-left page-post-detail">
    <div class="headband"></div>
    <a href="https://github.com/zlleige" target="_blank" rel="noopener" class="github-corner" aria-label="View source on GitHub"><svg width="80" height="80" viewBox="0 0 250 250" style="fill:#70B7FD; color:#fff; position: absolute; top: 0; border: 0; right: 0;" aria-hidden="true"><path d="M0,0 L115,115 L130,115 L142,142 L250,250 L250,0 Z"></path><path d="M128.3,109.0 C113.8,99.7 119.0,89.6 119.0,89.6 C122.0,82.7 120.5,78.6 120.5,78.6 C119.2,72.0 123.4,76.3 123.4,76.3 C127.3,80.9 125.5,87.3 125.5,87.3 C122.9,97.6 130.6,101.9 134.4,103.2" fill="currentColor" style="transform-origin: 130px 106px;" class="octo-arm"></path><path d="M115.0,115.0 C114.9,115.1 118.7,116.5 119.8,115.4 L133.7,101.6 C136.9,99.2 139.9,98.4 142.2,98.6 C133.8,88.0 127.5,74.4 143.8,58.0 C148.5,53.4 154.0,51.2 159.7,51.0 C160.3,49.4 163.2,43.6 171.4,40.1 C171.4,40.1 176.1,42.5 178.8,56.2 C183.1,58.6 187.2,61.8 190.9,65.4 C194.5,69.0 197.7,73.2 200.1,77.6 C213.8,80.2 216.3,84.9 216.3,84.9 C212.7,93.1 206.9,96.0 205.4,96.6 C205.1,102.4 203.0,107.8 198.3,112.5 C181.9,128.9 168.3,122.5 157.7,114.1 C157.9,116.9 156.7,120.9 152.7,124.9 L141.0,136.5 C139.8,137.7 141.6,141.9 141.8,141.8 Z" fill="currentColor" class="octo-body"></path></svg></a><style>.github-corner:hover .octo-arm{animation:octocat-wave 560ms ease-in-out}@keyframes octocat-wave{0%,100%{transform:rotate(0)}20%,60%{transform:rotate(-25deg)}40%,80%{transform:rotate(10deg)}}@media (max-width:500px){.github-corner:hover .octo-arm{animation:none}.github-corner .octo-arm{animation:octocat-wave 560ms ease-in-out}}</style>

    <header id="header" class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-brand-wrapper">
  <div class="site-meta ">
    

    <div class="custom-logo-site-title">
      <a href="/"  class="brand" rel="start">
        <span class="logo-line-before"><i></i></span>
        <span class="site-title">磊迹</span>
        <span class="logo-line-after"><i></i></span>
      </a>
    </div>
      
        <p class="site-subtitle">热爱可抵岁月漫长</p>
      
  </div>

  <div class="site-nav-toggle">
    <button>
      <span class="btn-bar"></span>
      <span class="btn-bar"></span>
      <span class="btn-bar"></span>
    </button>
  </div>
</div>

<nav class="site-nav">
  

  
    <ul id="menu" class="menu">
      
        
        <li class="menu-item menu-item-home">
          <a href="/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-home"></i> <br />
            
            首页
          </a>
        </li>
      
        
        <li class="menu-item menu-item-about">
          <a href="/about/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-user"></i> <br />
            
            关于
          </a>
        </li>
      
        
        <li class="menu-item menu-item-tags">
          <a href="/tags/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-tags"></i> <br />
            
            标签
          </a>
        </li>
      
        
        <li class="menu-item menu-item-categories">
          <a href="/categories/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-th"></i> <br />
            
            分类
          </a>
        </li>
      
        
        <li class="menu-item menu-item-archives">
          <a href="/archives/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-archive"></i> <br />
            
            归档
          </a>
        </li>
      

      
        <li class="menu-item menu-item-search">
          
            <a href="javascript:;" class="popup-trigger">
          
            
              <i class="menu-item-icon fa fa-search fa-fw"></i> <br />
            
            搜索
          </a>
        </li>
      
    </ul>
  

  
    <div class="site-search">
      
  <div class="popup search-popup local-search-popup">
  <div class="local-search-header clearfix">
    <span class="search-icon">
      <i class="fa fa-search"></i>
    </span>
    <span class="popup-btn-close">
      <i class="fa fa-times-circle"></i>
    </span>
    <div class="local-search-input-wrapper">
      <input autocomplete="off"
             placeholder="搜索..." spellcheck="false"
             type="text" id="local-search-input">
    </div>
  </div>
  <div id="local-search-result"></div>
</div>



    </div>
  
</nav>



 </div>
    </header>

    <main id="main" class="main">
      <div class="main-inner">
        <div class="content-wrap">
          <div id="content" class="content">
            

  <div id="posts" class="posts-expand">
    

  

  
  
  

  <article class="post post-type-normal" itemscope itemtype="http://schema.org/Article">
  
  
  
  <div class="post-block">
    <link itemprop="mainEntityOfPage" href="http://yoursite.com/%E6%95%B0%E6%8D%AE%E5%BA%93/%E6%95%B0%E6%8D%AE%E5%BA%93-SQL/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="name" content="Lei Zhou">
      <meta itemprop="description" content="">
      <meta itemprop="image" content="/images/title.gif">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="磊迹">
    </span>

    
      <header class="post-header">

        
        
          <h1 class="post-title" itemprop="name headline">数据库——SQL</h1>
        

        <div class="post-meta">
          <span class="post-time">
            
              <span class="post-meta-item-icon">
                <i class="fa fa-calendar-o"></i>
              </span>
              
                <span class="post-meta-item-text">发表于</span>
              
              <time title="创建于" itemprop="dateCreated datePublished" datetime="2019-10-25T00:00:00+08:00">
                2019-10-25
              </time>
            

            

            
          </span>

          
            <span class="post-category" >
            
              <span class="post-meta-divider">|</span>
            
              <span class="post-meta-item-icon">
                <i class="fa fa-folder-o"></i>
              </span>
              
                <span class="post-meta-item-text">分类于</span>
              
              
                <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
                  <a href="/categories/%E6%95%B0%E6%8D%AE%E5%BA%93/" itemprop="url" rel="index">
                    <span itemprop="name">数据库</span>
                  </a>
                </span>

                
                
                  ， 
                
              
                <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
                  <a href="/categories/%E6%95%B0%E6%8D%AE%E5%BA%93/SQL/" itemprop="url" rel="index">
                    <span itemprop="name">SQL</span>
                  </a>
                </span>

                
                
              
            </span>
          

          
            
              <span class="post-comments-count">
                <span class="post-meta-divider">|</span>
                <span class="post-meta-item-icon">
                  <i class="fa fa-comment-o"></i>
                </span>
                <a href="/%E6%95%B0%E6%8D%AE%E5%BA%93/%E6%95%B0%E6%8D%AE%E5%BA%93-SQL/#comments" itemprop="discussionUrl">
                  <span class="post-comments-count valine-comment-count" data-xid="/%E6%95%B0%E6%8D%AE%E5%BA%93/%E6%95%B0%E6%8D%AE%E5%BA%93-SQL/" itemprop="commentCount"></span>
                </a>
              </span>
            
          

          
          

          

          
            <div class="post-wordcount">
              
                
                <span class="post-meta-item-icon">
                  <i class="fa fa-file-word-o"></i>
                </span>
                
                  <span class="post-meta-item-text">字数统计&#58;</span>
                
                <span title="字数统计">
                  4k
                </span>
              

              
                <span class="post-meta-divider">|</span>
              

              
                <span class="post-meta-item-icon">
                  <i class="fa fa-clock-o"></i>
                </span>
                
                  <span class="post-meta-item-text">阅读时长 &asymp;</span>
                
                <span title="阅读时长">
                  15
                </span>
              
            </div>
          

          
              <div class="post-description">
                  浅谈数据库
              </div>
          

        </div>
      </header>
    

    
    
    
    <div class="post-body" itemprop="articleBody">

      
      

      
        <!-- GFM-TOC -->

<ul>
<li><a href="#一基础">一、基础</a></li>
<li><a href="#二创建表">二、创建表</a></li>
<li><a href="#三修改表">三、修改表</a></li>
<li><a href="#四插入">四、插入</a></li>
<li><a href="#五更新">五、更新</a></li>
<li><a href="#六删除">六、删除</a></li>
<li><a href="#七查询">七、查询</a></li>
<li><a href="#八排序">八、排序</a></li>
<li><a href="#九过滤">九、过滤</a></li>
<li><a href="#十通配符">十、通配符</a></li>
<li><a href="#十一计算字段">十一、计算字段</a></li>
<li><a href="#十二函数">十二、函数</a></li>
<li><a href="#十三分组">十三、分组</a></li>
<li><a href="#十四子查询">十四、子查询</a></li>
<li><a href="#十五连接">十五、连接</a></li>
<li><a href="#十六组合查询">十六、组合查询</a></li>
<li><a href="#十七视图">十七、视图</a></li>
<li><a href="#十八存储过程">十八、存储过程</a></li>
<li><a href="#十九游标">十九、游标</a></li>
<li><a href="#二十触发器">二十、触发器</a></li>
<li><a href="#二十一事务管理">二十一、事务管理</a></li>
<li><a href="#二十二字符集">二十二、字符集</a></li>
<li><a href="#二十三权限管理">二十三、权限管理</a></li>
<li><a href="#参考资料">参考资料</a><!-- GFM-TOC -->


</li>
</ul>
<h1 id="一、基础"><a href="#一、基础" class="headerlink" title="一、基础"></a>一、基础</h1><p>模式定义了数据如何存储、存储什么样的数据以及数据如何分解等信息，数据库和表都有模式。</p>
<p>主键的值不允许修改，也不允许复用（不能将已经删除的主键值赋给新数据行的主键）。</p>
<p>SQL（Structured Query Language)，标准 SQL 由 ANSI 标准委员会管理，从而称为 ANSI SQL。各个 DBMS 都有自己的实现，如 PL/SQL、Transact-SQL 等。</p>
<p>SQL 语句不区分大小写，但是数据库表名、列名和值是否区分依赖于具体的 DBMS 以及配置。</p>
<p>SQL 支持以下三种注释：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 注释</span></span><br><span class="line"><span class="keyword">SELECT</span> *</span><br><span class="line"><span class="keyword">FROM</span> mytable; <span class="comment">-- 注释</span></span><br><span class="line"><span class="comment">/* 注释1</span></span><br><span class="line"><span class="comment">   注释2 */</span></span><br></pre></td></tr></table></figure>

<p>数据库创建与使用：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">DATABASE</span> <span class="keyword">test</span>;</span><br><span class="line"><span class="keyword">USE</span> <span class="keyword">test</span>;</span><br></pre></td></tr></table></figure>

<h1 id="二、创建表"><a href="#二、创建表" class="headerlink" title="二、创建表"></a>二、创建表</h1><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> mytable (</span><br><span class="line">  <span class="comment"># int 类型，不为空，自增</span></span><br><span class="line">  <span class="keyword">id</span> <span class="built_in">INT</span> <span class="keyword">NOT</span> <span class="literal">NULL</span> AUTO_INCREMENT,</span><br><span class="line">  <span class="comment"># int 类型，不可为空，默认值为 1，不为空</span></span><br><span class="line">  col1 <span class="built_in">INT</span> <span class="keyword">NOT</span> <span class="literal">NULL</span> <span class="keyword">DEFAULT</span> <span class="number">1</span>,</span><br><span class="line">  <span class="comment"># 变长字符串类型，最长为 45 个字符，可以为空</span></span><br><span class="line">  col2 <span class="built_in">VARCHAR</span>(<span class="number">45</span>) <span class="literal">NULL</span>,</span><br><span class="line">  <span class="comment"># 日期类型，可为空</span></span><br><span class="line">  col3 <span class="built_in">DATE</span> <span class="literal">NULL</span>,</span><br><span class="line">  <span class="comment"># 设置主键为 id</span></span><br><span class="line">  PRIMARY <span class="keyword">KEY</span> (<span class="string">`id`</span>));</span><br></pre></td></tr></table></figure>

<h1 id="三、修改表"><a href="#三、修改表" class="headerlink" title="三、修改表"></a>三、修改表</h1><p>添加列</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> mytable</span><br><span class="line"><span class="keyword">ADD</span> <span class="keyword">col</span> <span class="built_in">CHAR</span>(<span class="number">20</span>);</span><br></pre></td></tr></table></figure>

<p>删除列</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> mytable</span><br><span class="line"><span class="keyword">DROP</span> <span class="keyword">COLUMN</span> <span class="keyword">col</span>;</span><br></pre></td></tr></table></figure>

<p>删除表</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DROP</span> <span class="keyword">TABLE</span> mytable;</span><br></pre></td></tr></table></figure>

<h1 id="四、插入"><a href="#四、插入" class="headerlink" title="四、插入"></a>四、插入</h1><p>普通插入</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> mytable(col1, col2)</span><br><span class="line"><span class="keyword">VALUES</span>(val1, val2);</span><br></pre></td></tr></table></figure>

<p>插入检索出来的数据</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> mytable1(col1, col2)</span><br><span class="line"><span class="keyword">SELECT</span> col1, col2</span><br><span class="line"><span class="keyword">FROM</span> mytable2;</span><br></pre></td></tr></table></figure>

<p>将一个表的内容插入到一个新表</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> newtable <span class="keyword">AS</span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> mytable;</span><br></pre></td></tr></table></figure>

<h1 id="五、更新"><a href="#五、更新" class="headerlink" title="五、更新"></a>五、更新</h1><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">UPDATE</span> mytable</span><br><span class="line"><span class="keyword">SET</span> <span class="keyword">col</span> = val</span><br><span class="line"><span class="keyword">WHERE</span> <span class="keyword">id</span> = <span class="number">1</span>;</span><br></pre></td></tr></table></figure>

<h1 id="六、删除"><a href="#六、删除" class="headerlink" title="六、删除"></a>六、删除</h1><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DELETE</span> <span class="keyword">FROM</span> mytable</span><br><span class="line"><span class="keyword">WHERE</span> <span class="keyword">id</span> = <span class="number">1</span>;</span><br></pre></td></tr></table></figure>

<p><strong>TRUNCATE TABLE</strong>   可以清空表，也就是删除所有行。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">TRUNCATE</span> <span class="keyword">TABLE</span> mytable;</span><br></pre></td></tr></table></figure>

<p>使用更新和删除操作时一定要用 WHERE 子句，不然会把整张表的数据都破坏。可以先用 SELECT 语句进行测试，防止错误删除。</p>
<h1 id="七、查询"><a href="#七、查询" class="headerlink" title="七、查询"></a>七、查询</h1><h2 id="DISTINCT"><a href="#DISTINCT" class="headerlink" title="DISTINCT"></a>DISTINCT</h2><p>相同值只会出现一次。它作用于所有列，也就是说所有列的值都相同才算相同。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="keyword">DISTINCT</span> col1, col2</span><br><span class="line"><span class="keyword">FROM</span> mytable;</span><br></pre></td></tr></table></figure>

<h2 id="LIMIT"><a href="#LIMIT" class="headerlink" title="LIMIT"></a>LIMIT</h2><p>限制返回的行数。可以有两个参数，第一个参数为起始行，从 0 开始；第二个参数为返回的总行数。</p>
<p>返回前 5 行：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> *</span><br><span class="line"><span class="keyword">FROM</span> mytable</span><br><span class="line"><span class="keyword">LIMIT</span> <span class="number">5</span>;</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> *</span><br><span class="line"><span class="keyword">FROM</span> mytable</span><br><span class="line"><span class="keyword">LIMIT</span> <span class="number">0</span>, <span class="number">5</span>;</span><br></pre></td></tr></table></figure>

<p>返回第 3 ~ 5 行：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> *</span><br><span class="line"><span class="keyword">FROM</span> mytable</span><br><span class="line"><span class="keyword">LIMIT</span> <span class="number">2</span>, <span class="number">3</span>;</span><br></pre></td></tr></table></figure>

<h1 id="八、排序"><a href="#八、排序" class="headerlink" title="八、排序"></a>八、排序</h1><ul>
<li><strong>ASC</strong>  ：升序（默认）</li>
<li><strong>DESC</strong>  ：降序</li>
</ul>
<p>可以按多个列进行排序，并且为每个列指定不同的排序方式：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> *</span><br><span class="line"><span class="keyword">FROM</span> mytable</span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> col1 <span class="keyword">DESC</span>, col2 <span class="keyword">ASC</span>;</span><br></pre></td></tr></table></figure>

<h1 id="九、过滤"><a href="#九、过滤" class="headerlink" title="九、过滤"></a>九、过滤</h1><p>不进行过滤的数据非常大，导致通过网络传输了多余的数据，从而浪费了网络带宽。因此尽量使用 SQL 语句来过滤不必要的数据，而不是传输所有的数据到客户端中然后由客户端进行过滤。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> *</span><br><span class="line"><span class="keyword">FROM</span> mytable</span><br><span class="line"><span class="keyword">WHERE</span> <span class="keyword">col</span> <span class="keyword">IS</span> <span class="literal">NULL</span>;</span><br></pre></td></tr></table></figure>

<p>下表显示了 WHERE 子句可用的操作符</p>
<table>
<thead>
<tr>
<th align="center">操作符</th>
<th align="center">说明</th>
</tr>
</thead>
<tbody><tr>
<td align="center">=</td>
<td align="center">等于</td>
</tr>
<tr>
<td align="center">&lt;</td>
<td align="center">小于</td>
</tr>
<tr>
<td align="center">&gt;</td>
<td align="center">大于</td>
</tr>
<tr>
<td align="center">&lt;&gt; !=</td>
<td align="center">不等于</td>
</tr>
<tr>
<td align="center">&lt;= !&gt;</td>
<td align="center">小于等于</td>
</tr>
<tr>
<td align="center">&gt;= !&lt;</td>
<td align="center">大于等于</td>
</tr>
<tr>
<td align="center">BETWEEN</td>
<td align="center">在两个值之间</td>
</tr>
<tr>
<td align="center">IS NULL</td>
<td align="center">为 NULL 值</td>
</tr>
</tbody></table>
<p>应该注意到，NULL 与 0、空字符串都不同。</p>
<p><strong>AND 和 OR</strong>   用于连接多个过滤条件。优先处理 AND，当一个过滤表达式涉及到多个 AND 和 OR 时，可以使用 () 来决定优先级，使得优先级关系更清晰。</p>
<p><strong>IN</strong>   操作符用于匹配一组值，其后也可以接一个 SELECT 子句，从而匹配子查询得到的一组值。</p>
<p><strong>NOT</strong>   操作符用于否定一个条件。</p>
<h1 id="十、通配符"><a href="#十、通配符" class="headerlink" title="十、通配符"></a>十、通配符</h1><p>通配符也是用在过滤语句中，但它只能用于文本字段。</p>
<ul>
<li><p><strong>%</strong>   匹配 &gt;=0 个任意字符；</p>
</li>
<li><p><strong>_</strong>   匹配 ==1 个任意字符；</p>
</li>
<li><p><strong>[ ]</strong>   可以匹配集合内的字符，例如 [ab] 将匹配字符 a 或者 b。用脱字符 ^ 可以对其进行否定，也就是不匹配集合内的字符。</p>
</li>
</ul>
<p>使用 Like 来进行通配符匹配。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> *</span><br><span class="line"><span class="keyword">FROM</span> mytable</span><br><span class="line"><span class="keyword">WHERE</span> <span class="keyword">col</span> <span class="keyword">LIKE</span> <span class="string">'[^AB]%'</span>; <span class="comment">-- 不以 A 和 B 开头的任意文本</span></span><br></pre></td></tr></table></figure>

<p>不要滥用通配符，通配符位于开头处匹配会非常慢。</p>
<h1 id="十一、计算字段"><a href="#十一、计算字段" class="headerlink" title="十一、计算字段"></a>十一、计算字段</h1><p>在数据库服务器上完成数据的转换和格式化的工作往往比客户端上快得多，并且转换和格式化后的数据量更少的话可以减少网络通信量。</p>
<p>计算字段通常需要使用   <strong>AS</strong>   来取别名，否则输出的时候字段名为计算表达式。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> col1 * col2 <span class="keyword">AS</span> <span class="keyword">alias</span></span><br><span class="line"><span class="keyword">FROM</span> mytable;</span><br></pre></td></tr></table></figure>

<p><strong>CONCAT()</strong>   用于连接两个字段。许多数据库会使用空格把一个值填充为列宽，因此连接的结果会出现一些不必要的空格，使用 <strong>TRIM()</strong> 可以去除首尾空格。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="keyword">CONCAT</span>(<span class="keyword">TRIM</span>(col1), <span class="string">'('</span>, <span class="keyword">TRIM</span>(col2), <span class="string">')'</span>) <span class="keyword">AS</span> concat_col</span><br><span class="line"><span class="keyword">FROM</span> mytable;</span><br></pre></td></tr></table></figure>

<h1 id="十二、函数"><a href="#十二、函数" class="headerlink" title="十二、函数"></a>十二、函数</h1><p>各个 DBMS 的函数都是不相同的，因此不可移植，以下主要是 MySQL 的函数。</p>
<h2 id="汇总"><a href="#汇总" class="headerlink" title="汇总"></a>汇总</h2><table>
<thead>
<tr>
<th align="center">函 数</th>
<th align="center">说 明</th>
</tr>
</thead>
<tbody><tr>
<td align="center">AVG()</td>
<td align="center">返回某列的平均值</td>
</tr>
<tr>
<td align="center">COUNT()</td>
<td align="center">返回某列的行数</td>
</tr>
<tr>
<td align="center">MAX()</td>
<td align="center">返回某列的最大值</td>
</tr>
<tr>
<td align="center"></td>
<td align="center">返回某列的最小值</td>
</tr>
<tr>
<td align="center">SUM()</td>
<td align="center">返回某列值之和</td>
</tr>
</tbody></table>
<p>AVG() 会忽略 NULL 行。</p>
<p>使用 DISTINCT 可以汇总不同的值。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="keyword">AVG</span>(<span class="keyword">DISTINCT</span> col1) <span class="keyword">AS</span> avg_col</span><br><span class="line"><span class="keyword">FROM</span> mytable;</span><br></pre></td></tr></table></figure>

<h2 id="文本处理"><a href="#文本处理" class="headerlink" title="文本处理"></a>文本处理</h2><table>
<thead>
<tr>
<th align="center">函数</th>
<th align="center">说明</th>
</tr>
</thead>
<tbody><tr>
<td align="center">LEFT()</td>
<td align="center">左边的字符</td>
</tr>
<tr>
<td align="center">RIGHT()</td>
<td align="center">右边的字符</td>
</tr>
<tr>
<td align="center">LOWER()</td>
<td align="center">转换为小写字符</td>
</tr>
<tr>
<td align="center">UPPER()</td>
<td align="center">转换为大写字符</td>
</tr>
<tr>
<td align="center">LTRIM()</td>
<td align="center">去除左边的空格</td>
</tr>
<tr>
<td align="center">RTRIM()</td>
<td align="center">去除右边的空格</td>
</tr>
<tr>
<td align="center">LENGTH()</td>
<td align="center">长度</td>
</tr>
<tr>
<td align="center">SOUNDEX()</td>
<td align="center">转换为语音值</td>
</tr>
</tbody></table>
<p>其中，  <strong>SOUNDEX()</strong>   可以将一个字符串转换为描述其语音表示的字母数字模式。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> *</span><br><span class="line"><span class="keyword">FROM</span> mytable</span><br><span class="line"><span class="keyword">WHERE</span> <span class="keyword">SOUNDEX</span>(col1) = <span class="keyword">SOUNDEX</span>(<span class="string">'apple'</span>)</span><br></pre></td></tr></table></figure>

<h2 id="日期和时间处理"><a href="#日期和时间处理" class="headerlink" title="日期和时间处理"></a>日期和时间处理</h2><ul>
<li>日期格式：YYYY-MM-DD</li>
<li>时间格式：HH:<zero-width space>MM:SS</zero-width></li>
</ul>
<table>
<thead>
<tr>
<th align="center">函 数</th>
<th align="center">说 明</th>
</tr>
</thead>
<tbody><tr>
<td align="center">ADDDATE()</td>
<td align="center">增加一个日期（天、周等）</td>
</tr>
<tr>
<td align="center">ADDTIME()</td>
<td align="center">增加一个时间（时、分等）</td>
</tr>
<tr>
<td align="center">CURDATE()</td>
<td align="center">返回当前日期</td>
</tr>
<tr>
<td align="center">CURTIME()</td>
<td align="center">返回当前时间</td>
</tr>
<tr>
<td align="center">DATE()</td>
<td align="center">返回日期时间的日期部分</td>
</tr>
<tr>
<td align="center">DATEDIFF()</td>
<td align="center">计算两个日期之差</td>
</tr>
<tr>
<td align="center">DATE_ADD()</td>
<td align="center">高度灵活的日期运算函数</td>
</tr>
<tr>
<td align="center">DATE_FORMAT()</td>
<td align="center">返回一个格式化的日期或时间串</td>
</tr>
<tr>
<td align="center">DAY()</td>
<td align="center">返回一个日期的天数部分</td>
</tr>
<tr>
<td align="center">DAYOFWEEK()</td>
<td align="center">对于一个日期，返回对应的星期几</td>
</tr>
<tr>
<td align="center">HOUR()</td>
<td align="center">返回一个时间的小时部分</td>
</tr>
<tr>
<td align="center">MINUTE()</td>
<td align="center">返回一个时间的分钟部分</td>
</tr>
<tr>
<td align="center">MONTH()</td>
<td align="center">返回一个日期的月份部分</td>
</tr>
<tr>
<td align="center">NOW()</td>
<td align="center">返回当前日期和时间</td>
</tr>
<tr>
<td align="center">SECOND()</td>
<td align="center">返回一个时间的秒部分</td>
</tr>
<tr>
<td align="center">TIME()</td>
<td align="center">返回一个日期时间的时间部分</td>
</tr>
<tr>
<td align="center">YEAR()</td>
<td align="center">返回一个日期的年份部分</td>
</tr>
</tbody></table>
<figure class="highlight"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; SELECT NOW();</span><br></pre></td></tr></table></figure>

<figure class="highlight angelscript"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="number">2018</span><span class="number">-4</span><span class="number">-14</span> <span class="number">20</span>:<span class="number">25</span>:<span class="number">11</span></span><br></pre></td></tr></table></figure>

<h2 id="数值处理"><a href="#数值处理" class="headerlink" title="数值处理"></a>数值处理</h2><table>
<thead>
<tr>
<th align="center">函数</th>
<th align="center">说明</th>
</tr>
</thead>
<tbody><tr>
<td align="center">SIN()</td>
<td align="center">正弦</td>
</tr>
<tr>
<td align="center">COS()</td>
<td align="center">余弦</td>
</tr>
<tr>
<td align="center">TAN()</td>
<td align="center">正切</td>
</tr>
<tr>
<td align="center">ABS()</td>
<td align="center">绝对值</td>
</tr>
<tr>
<td align="center">SQRT()</td>
<td align="center">平方根</td>
</tr>
<tr>
<td align="center">MOD()</td>
<td align="center">余数</td>
</tr>
<tr>
<td align="center">EXP()</td>
<td align="center">指数</td>
</tr>
<tr>
<td align="center">PI()</td>
<td align="center">圆周率</td>
</tr>
<tr>
<td align="center">RAND()</td>
<td align="center">随机数</td>
</tr>
</tbody></table>
<h1 id="十三、分组"><a href="#十三、分组" class="headerlink" title="十三、分组"></a>十三、分组</h1><p>把具有相同的数据值的行放在同一组中。</p>
<p>可以对同一分组数据使用汇总函数进行处理，例如求分组数据的平均值等。</p>
<p>指定的分组字段除了能按该字段进行分组，也会自动按该字段进行排序。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="keyword">col</span>, <span class="keyword">COUNT</span>(*) <span class="keyword">AS</span> <span class="keyword">num</span></span><br><span class="line"><span class="keyword">FROM</span> mytable</span><br><span class="line"><span class="keyword">GROUP</span> <span class="keyword">BY</span> <span class="keyword">col</span>;</span><br></pre></td></tr></table></figure>

<p>GROUP BY 自动按分组字段进行排序，ORDER BY 也可以按汇总字段来进行排序。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="keyword">col</span>, <span class="keyword">COUNT</span>(*) <span class="keyword">AS</span> <span class="keyword">num</span></span><br><span class="line"><span class="keyword">FROM</span> mytable</span><br><span class="line"><span class="keyword">GROUP</span> <span class="keyword">BY</span> <span class="keyword">col</span></span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> <span class="keyword">num</span>;</span><br></pre></td></tr></table></figure>

<p>WHERE 过滤行，HAVING 过滤分组，行过滤应当先于分组过滤。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="keyword">col</span>, <span class="keyword">COUNT</span>(*) <span class="keyword">AS</span> <span class="keyword">num</span></span><br><span class="line"><span class="keyword">FROM</span> mytable</span><br><span class="line"><span class="keyword">WHERE</span> <span class="keyword">col</span> &gt; <span class="number">2</span></span><br><span class="line"><span class="keyword">GROUP</span> <span class="keyword">BY</span> <span class="keyword">col</span></span><br><span class="line"><span class="keyword">HAVING</span> <span class="keyword">num</span> &gt;= <span class="number">2</span>;</span><br></pre></td></tr></table></figure>

<p>分组规定：</p>
<ul>
<li>GROUP BY 子句出现在 WHERE 子句之后，ORDER BY 子句之前；</li>
<li>除了汇总字段外，SELECT 语句中的每一字段都必须在 GROUP BY 子句中给出；</li>
<li>NULL 的行会单独分为一组；</li>
<li>大多数 SQL 实现不支持 GROUP BY 列具有可变长度的数据类型。</li>
</ul>
<h1 id="十四、子查询"><a href="#十四、子查询" class="headerlink" title="十四、子查询"></a>十四、子查询</h1><p>子查询中只能返回一个字段的数据。</p>
<p>可以将子查询的结果作为 WHRER 语句的过滤条件：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> *</span><br><span class="line"><span class="keyword">FROM</span> mytable1</span><br><span class="line"><span class="keyword">WHERE</span> col1 <span class="keyword">IN</span> (<span class="keyword">SELECT</span> col2</span><br><span class="line">               <span class="keyword">FROM</span> mytable2);</span><br></pre></td></tr></table></figure>

<p>下面的语句可以检索出客户的订单数量，子查询语句会对第一个查询检索出的每个客户执行一次：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> cust_name, (<span class="keyword">SELECT</span> <span class="keyword">COUNT</span>(*)</span><br><span class="line">                   <span class="keyword">FROM</span> Orders</span><br><span class="line">                   <span class="keyword">WHERE</span> Orders.cust_id = Customers.cust_id)</span><br><span class="line">                   <span class="keyword">AS</span> orders_num</span><br><span class="line"><span class="keyword">FROM</span> Customers</span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> cust_name;</span><br></pre></td></tr></table></figure>

<h1 id="十五、连接"><a href="#十五、连接" class="headerlink" title="十五、连接"></a>十五、连接</h1><p>连接用于连接多个表，使用 JOIN 关键字，并且条件语句使用 ON 而不是 WHERE。</p>
<p>连接可以替换子查询，并且比子查询的效率一般会更快。</p>
<p>可以用 AS 给列名、计算字段和表名取别名，给表名取别名是为了简化 SQL 语句以及连接相同表。</p>
<h2 id="内连接"><a href="#内连接" class="headerlink" title="内连接"></a>内连接</h2><p>内连接又称等值连接，使用 INNER JOIN 关键字。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> A.value, B.value</span><br><span class="line"><span class="keyword">FROM</span> tablea <span class="keyword">AS</span> A <span class="keyword">INNER</span> <span class="keyword">JOIN</span> tableb <span class="keyword">AS</span> B</span><br><span class="line"><span class="keyword">ON</span> A.key = B.key;</span><br></pre></td></tr></table></figure>

<p>可以不明确使用 INNER JOIN，而使用普通查询并在 WHERE 中将两个表中要连接的列用等值方法连接起来。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> A.value, B.value</span><br><span class="line"><span class="keyword">FROM</span> tablea <span class="keyword">AS</span> A, tableb <span class="keyword">AS</span> B</span><br><span class="line"><span class="keyword">WHERE</span> A.key = B.key;</span><br></pre></td></tr></table></figure>

<h2 id="自连接"><a href="#自连接" class="headerlink" title="自连接"></a>自连接</h2><p>自连接可以看成内连接的一种，只是连接的表是自身而已。</p>
<p>一张员工表，包含员工姓名和员工所属部门，要找出与 Jim 处在同一部门的所有员工姓名。</p>
<p>子查询版本</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="keyword">name</span></span><br><span class="line"><span class="keyword">FROM</span> employee</span><br><span class="line"><span class="keyword">WHERE</span> department = (</span><br><span class="line">      <span class="keyword">SELECT</span> department</span><br><span class="line">      <span class="keyword">FROM</span> employee</span><br><span class="line">      <span class="keyword">WHERE</span> <span class="keyword">name</span> = <span class="string">"Jim"</span>);</span><br></pre></td></tr></table></figure>

<p>自连接版本</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> e1.name</span><br><span class="line"><span class="keyword">FROM</span> employee <span class="keyword">AS</span> e1 <span class="keyword">INNER</span> <span class="keyword">JOIN</span> employee <span class="keyword">AS</span> e2</span><br><span class="line"><span class="keyword">ON</span> e1.department = e2.department</span><br><span class="line">      <span class="keyword">AND</span> e2.name = <span class="string">"Jim"</span>;</span><br></pre></td></tr></table></figure>

<h2 id="自然连接"><a href="#自然连接" class="headerlink" title="自然连接"></a>自然连接</h2><p>自然连接是把同名列通过等值测试连接起来的，同名列可以有多个。</p>
<p>内连接和自然连接的区别：内连接提供连接的列，而自然连接自动连接所有同名列。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> A.value, B.value</span><br><span class="line"><span class="keyword">FROM</span> tablea <span class="keyword">AS</span> A <span class="keyword">NATURAL</span> <span class="keyword">JOIN</span> tableb <span class="keyword">AS</span> B;</span><br></pre></td></tr></table></figure>

<h2 id="外连接"><a href="#外连接" class="headerlink" title="外连接"></a>外连接</h2><p>外连接保留了没有关联的那些行。分为左外连接，右外连接以及全外连接，左外连接就是保留左表没有关联的行。</p>
<p>检索所有顾客的订单信息，包括还没有订单信息的顾客。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> Customers.cust_id, Orders.order_num</span><br><span class="line"><span class="keyword">FROM</span> Customers <span class="keyword">LEFT</span> <span class="keyword">OUTER</span> <span class="keyword">JOIN</span> Orders</span><br><span class="line"><span class="keyword">ON</span> Customers.cust_id = Orders.cust_id;</span><br></pre></td></tr></table></figure>

<p>customers 表：</p>
<table>
<thead>
<tr>
<th align="center">cust_id</th>
<th align="center">cust_name</th>
</tr>
</thead>
<tbody><tr>
<td align="center">1</td>
<td align="center">a</td>
</tr>
<tr>
<td align="center">2</td>
<td align="center">b</td>
</tr>
<tr>
<td align="center">3</td>
<td align="center">c</td>
</tr>
</tbody></table>
<p>orders 表：</p>
<table>
<thead>
<tr>
<th align="center">order_id</th>
<th align="center">cust_id</th>
</tr>
</thead>
<tbody><tr>
<td align="center">1</td>
<td align="center">1</td>
</tr>
<tr>
<td align="center">2</td>
<td align="center">1</td>
</tr>
<tr>
<td align="center">3</td>
<td align="center">3</td>
</tr>
<tr>
<td align="center">4</td>
<td align="center">3</td>
</tr>
</tbody></table>
<p>结果：</p>
<table>
<thead>
<tr>
<th align="center">cust_id</th>
<th align="center">cust_name</th>
<th align="center">order_id</th>
</tr>
</thead>
<tbody><tr>
<td align="center">1</td>
<td align="center">a</td>
<td align="center">1</td>
</tr>
<tr>
<td align="center">1</td>
<td align="center">a</td>
<td align="center">2</td>
</tr>
<tr>
<td align="center">3</td>
<td align="center">c</td>
<td align="center">3</td>
</tr>
<tr>
<td align="center">3</td>
<td align="center">c</td>
<td align="center">4</td>
</tr>
<tr>
<td align="center">2</td>
<td align="center">b</td>
<td align="center">Null</td>
</tr>
</tbody></table>
<h1 id="十六、组合查询"><a href="#十六、组合查询" class="headerlink" title="十六、组合查询"></a>十六、组合查询</h1><p>使用   <strong>UNION</strong>   来组合两个查询，如果第一个查询返回 M 行，第二个查询返回 N 行，那么组合查询的结果一般为 M+N 行。</p>
<p>每个查询必须包含相同的列、表达式和聚集函数。</p>
<p>默认会去除相同行，如果需要保留相同行，使用 UNION ALL。</p>
<p>只能包含一个 ORDER BY 子句，并且必须位于语句的最后。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="keyword">col</span></span><br><span class="line"><span class="keyword">FROM</span> mytable</span><br><span class="line"><span class="keyword">WHERE</span> <span class="keyword">col</span> = <span class="number">1</span></span><br><span class="line"><span class="keyword">UNION</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="keyword">col</span></span><br><span class="line"><span class="keyword">FROM</span> mytable</span><br><span class="line"><span class="keyword">WHERE</span> <span class="keyword">col</span> =<span class="number">2</span>;</span><br></pre></td></tr></table></figure>

<h1 id="十七、视图"><a href="#十七、视图" class="headerlink" title="十七、视图"></a>十七、视图</h1><p>视图是虚拟的表，本身不包含数据，也就不能对其进行索引操作。</p>
<p>对视图的操作和对普通表的操作一样。</p>
<p>视图具有如下好处：</p>
<ul>
<li>简化复杂的 SQL 操作，比如复杂的连接；</li>
<li>只使用实际表的一部分数据；</li>
<li>通过只给用户访问视图的权限，保证数据的安全性；</li>
<li>更改数据格式和表示。</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">VIEW</span> myview <span class="keyword">AS</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="keyword">Concat</span>(col1, col2) <span class="keyword">AS</span> concat_col, col3*col4 <span class="keyword">AS</span> compute_col</span><br><span class="line"><span class="keyword">FROM</span> mytable</span><br><span class="line"><span class="keyword">WHERE</span> col5 = val;</span><br></pre></td></tr></table></figure>

<h1 id="十八、存储过程"><a href="#十八、存储过程" class="headerlink" title="十八、存储过程"></a>十八、存储过程</h1><p>存储过程可以看成是对一系列 SQL 操作的批处理。</p>
<p>使用存储过程的好处：</p>
<ul>
<li>代码封装，保证了一定的安全性；</li>
<li>代码复用；</li>
<li>由于是预先编译，因此具有很高的性能。</li>
</ul>
<p>命令行中创建存储过程需要自定义分隔符，因为命令行是以 ; 为结束符，而存储过程中也包含了分号，因此会错误把这部分分号当成是结束符，造成语法错误。</p>
<p>包含 in、out 和 inout 三种参数。</p>
<p>给变量赋值都需要用 select into 语句。</p>
<p>每次只能给一个变量赋值，不支持集合的操作。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">delimiter //</span><br><span class="line"></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">procedure</span> myprocedure( <span class="keyword">out</span> ret <span class="built_in">int</span> )</span><br><span class="line">    <span class="keyword">begin</span></span><br><span class="line">        <span class="keyword">declare</span> y <span class="built_in">int</span>;</span><br><span class="line">        <span class="keyword">select</span> <span class="keyword">sum</span>(col1)</span><br><span class="line">        <span class="keyword">from</span> mytable</span><br><span class="line">        <span class="keyword">into</span> y;</span><br><span class="line">        <span class="keyword">select</span> y*y <span class="keyword">into</span> ret;</span><br><span class="line">    <span class="keyword">end</span> //</span><br><span class="line"></span><br><span class="line">delimiter ;</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">call</span> myprocedure(@ret);</span><br><span class="line"><span class="keyword">select</span> @ret;</span><br></pre></td></tr></table></figure>

<h1 id="十九、游标"><a href="#十九、游标" class="headerlink" title="十九、游标"></a>十九、游标</h1><p>在存储过程中使用游标可以对一个结果集进行移动遍历。</p>
<p>游标主要用于交互式应用，其中用户需要对数据集中的任意行进行浏览和修改。</p>
<p>使用游标的四个步骤：</p>
<ol>
<li>声明游标，这个过程没有实际检索出数据；</li>
<li>打开游标；</li>
<li>取出数据；</li>
<li>关闭游标；</li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br></pre></td><td class="code"><pre><span class="line">delimiter //</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">procedure</span> myprocedure(<span class="keyword">out</span> ret <span class="built_in">int</span>)</span><br><span class="line">    <span class="keyword">begin</span></span><br><span class="line">        <span class="keyword">declare</span> done <span class="built_in">boolean</span> <span class="keyword">default</span> <span class="number">0</span>;</span><br><span class="line"></span><br><span class="line">        <span class="keyword">declare</span> mycursor <span class="keyword">cursor</span> <span class="keyword">for</span></span><br><span class="line">        <span class="keyword">select</span> col1 <span class="keyword">from</span> mytable;</span><br><span class="line">        <span class="comment"># 定义了一个 continue handler，当 sqlstate '02000' 这个条件出现时，会执行 set done = 1</span></span><br><span class="line">        <span class="keyword">declare</span> continue <span class="keyword">handler</span> <span class="keyword">for</span> <span class="keyword">sqlstate</span> <span class="string">'02000'</span> <span class="keyword">set</span> done = <span class="number">1</span>;</span><br><span class="line"></span><br><span class="line">        open mycursor;</span><br><span class="line"></span><br><span class="line">        repeat</span><br><span class="line">            fetch mycursor into ret;</span><br><span class="line">            <span class="keyword">select</span> ret;</span><br><span class="line">        until done <span class="keyword">end</span> <span class="keyword">repeat</span>;</span><br><span class="line"></span><br><span class="line">        close mycursor;</span><br><span class="line">    <span class="keyword">end</span> //</span><br><span class="line"> delimiter ;</span><br></pre></td></tr></table></figure>

<h1 id="二十、触发器"><a href="#二十、触发器" class="headerlink" title="二十、触发器"></a>二十、触发器</h1><p>触发器会在某个表执行以下语句时而自动执行：DELETE、INSERT、UPDATE。</p>
<p>触发器必须指定在语句执行之前还是之后自动执行，之前执行使用 BEFORE 关键字，之后执行使用 AFTER 关键字。BEFORE 用于数据验证和净化，AFTER 用于审计跟踪，将修改记录到另外一张表中。</p>
<p>INSERT 触发器包含一个名为 NEW 的虚拟表。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TRIGGER</span> mytrigger <span class="keyword">AFTER</span> <span class="keyword">INSERT</span> <span class="keyword">ON</span> mytable</span><br><span class="line"><span class="keyword">FOR</span> <span class="keyword">EACH</span> <span class="keyword">ROW</span> <span class="keyword">SELECT</span> NEW.col <span class="keyword">into</span> @<span class="keyword">result</span>;</span><br><span class="line"></span><br><span class="line"><span class="keyword">SELECT</span> @<span class="keyword">result</span>; <span class="comment">-- 获取结果</span></span><br></pre></td></tr></table></figure>

<p>DELETE 触发器包含一个名为 OLD 的虚拟表，并且是只读的。</p>
<p>UPDATE 触发器包含一个名为 NEW 和一个名为 OLD 的虚拟表，其中 NEW 是可以被修改的，而 OLD 是只读的。</p>
<p>MySQL 不允许在触发器中使用 CALL 语句，也就是不能调用存储过程。</p>
<h1 id="二十一、事务管理"><a href="#二十一、事务管理" class="headerlink" title="二十一、事务管理"></a>二十一、事务管理</h1><p>基本术语：</p>
<ul>
<li>事务（transaction）指一组 SQL 语句；</li>
<li>回退（rollback）指撤销指定 SQL 语句的过程；</li>
<li>提交（commit）指将未存储的 SQL 语句结果写入数据库表；</li>
<li>保留点（savepoint）指事务处理中设置的临时占位符（placeholder），你可以对它发布回退（与回退整个事务处理不同）。</li>
</ul>
<p>不能回退 SELECT 语句，回退 SELECT 语句也没意义；也不能回退 CREATE 和 DROP 语句。</p>
<p>MySQL 的事务提交默认是隐式提交，每执行一条语句就把这条语句当成一个事务然后进行提交。当出现 START TRANSACTION 语句时，会关闭隐式提交；当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭，重新恢复隐式提交。</p>
<p>设置 autocommit 为 0 可以取消自动提交；autocommit 标记是针对每个连接而不是针对服务器的。</p>
<p>如果没有设置保留点，ROLLBACK 会回退到 START TRANSACTION 语句处；如果设置了保留点，并且在 ROLLBACK 中指定该保留点，则会回退到该保留点。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">START</span> <span class="keyword">TRANSACTION</span></span><br><span class="line">// ...</span><br><span class="line"><span class="keyword">SAVEPOINT</span> delete1</span><br><span class="line">// ...</span><br><span class="line"><span class="keyword">ROLLBACK</span> <span class="keyword">TO</span> delete1</span><br><span class="line">// ...</span><br><span class="line"><span class="keyword">COMMIT</span></span><br></pre></td></tr></table></figure>

<h1 id="二十二、字符集"><a href="#二十二、字符集" class="headerlink" title="二十二、字符集"></a>二十二、字符集</h1><p>基本术语：</p>
<ul>
<li>字符集为字母和符号的集合；</li>
<li>编码为某个字符集成员的内部表示；</li>
<li>校对字符指定如何比较，主要用于排序和分组。</li>
</ul>
<p>除了给表指定字符集和校对外，也可以给列指定：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> mytable</span><br><span class="line">(<span class="keyword">col</span> <span class="built_in">VARCHAR</span>(<span class="number">10</span>) <span class="built_in">CHARACTER</span> <span class="keyword">SET</span> latin <span class="keyword">COLLATE</span> latin1_general_ci )</span><br><span class="line"><span class="keyword">DEFAULT</span> <span class="built_in">CHARACTER</span> <span class="keyword">SET</span> hebrew <span class="keyword">COLLATE</span> hebrew_general_ci;</span><br></pre></td></tr></table></figure>

<p>可以在排序、分组时指定校对：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> *</span><br><span class="line"><span class="keyword">FROM</span> mytable</span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> <span class="keyword">col</span> <span class="keyword">COLLATE</span> latin1_general_ci;</span><br></pre></td></tr></table></figure>

<h1 id="二十三、权限管理"><a href="#二十三、权限管理" class="headerlink" title="二十三、权限管理"></a>二十三、权限管理</h1><p>MySQL 的账户信息保存在 mysql 这个数据库中。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">USE</span> mysql;</span><br><span class="line"><span class="keyword">SELECT</span> <span class="keyword">user</span> <span class="keyword">FROM</span> <span class="keyword">user</span>;</span><br></pre></td></tr></table></figure>

<p><strong>创建账户</strong>  </p>
<p>新创建的账户没有任何权限。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">USER</span> myuser <span class="keyword">IDENTIFIED</span> <span class="keyword">BY</span> <span class="string">'mypassword'</span>;</span><br></pre></td></tr></table></figure>

<p><strong>修改账户名</strong>  </p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">RENAME</span> <span class="keyword">USER</span> myuser <span class="keyword">TO</span> newuser;</span><br></pre></td></tr></table></figure>

<p><strong>删除账户</strong>  </p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DROP</span> <span class="keyword">USER</span> myuser;</span><br></pre></td></tr></table></figure>

<p><strong>查看权限</strong>  </p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SHOW</span> <span class="keyword">GRANTS</span> <span class="keyword">FOR</span> myuser;</span><br></pre></td></tr></table></figure>

<p><strong>授予权限</strong>  </p>
<p>账户用 username@host 的形式定义，username@% 使用的是默认主机名。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">GRANT</span> <span class="keyword">SELECT</span>, <span class="keyword">INSERT</span> <span class="keyword">ON</span> mydatabase.* <span class="keyword">TO</span> myuser;</span><br></pre></td></tr></table></figure>

<p><strong>删除权限</strong>  </p>
<p>GRANT 和 REVOKE 可在几个层次上控制访问权限：</p>
<ul>
<li>整个服务器，使用 GRANT ALL 和 REVOKE ALL；</li>
<li>整个数据库，使用 ON database.*；</li>
<li>特定的表，使用 ON database.table；</li>
<li>特定的列；</li>
<li>特定的存储过程。</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">REVOKE</span> <span class="keyword">SELECT</span>, <span class="keyword">INSERT</span> <span class="keyword">ON</span> mydatabase.* <span class="keyword">FROM</span> myuser;</span><br></pre></td></tr></table></figure>

<p><strong>更改密码</strong>  </p>
<p>必须使用 Password() 函数进行加密。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SET</span> PASSWROD <span class="keyword">FOR</span> myuser = <span class="keyword">Password</span>(<span class="string">'new_password'</span>);</span><br></pre></td></tr></table></figure>

<h1 id="参考资料"><a href="#参考资料" class="headerlink" title="参考资料"></a>参考资料</h1><ul>
<li>BenForta. SQL 必知必会 [M]. 人民邮电出版社, 2013.</li>
</ul>

      
    </div>
    
    
    

    

    
      <div>
        <div style="padding: 10px 0; margin: 20px auto; width: 90%; text-align: center;">
  <div>创作不易，欢迎打赏！</div>
  <button id="rewardButton" disable="enable" onclick="var qr = document.getElementById('QR'); if (qr.style.display === 'none') {qr.style.display='block';} else {qr.style.display='none'}">
    <span>打赏</span>
  </button>
  <div id="QR" style="display: none;">

    
      <div id="wechat" style="display: inline-block">
        <img id="wechat_qr" src="/images/wechatpay.jpg" alt="Lei Zhou 微信支付"/>
        <p>微信支付</p>
      </div>
    

    
      <div id="alipay" style="display: inline-block">
        <img id="alipay_qr" src="/images/alipay.jpg" alt="Lei Zhou 支付宝"/>
        <p>支付宝</p>
      </div>
    

    

  </div>
</div>

      </div>
    

    
<div>
  
    <div>
    
        <div style="text-align:center;color: #ccc;font-size:14px;">-------------本文结束<i class="fa fa-paw"></i>感谢您的阅读-------------</div>
    
</div>


  
</div>

    <footer class="post-footer">
      
        <div class="post-tags">
          
            <a href="/tags/%E6%95%B0%E6%8D%AE%E5%BA%93/" rel="tag"><i class="fa fa-tag"></i> 数据库</a>
          
            <a href="/tags/SQL/" rel="tag"><i class="fa fa-tag"></i> SQL</a>
          
        </div>
      

      
      
      

      
        <div class="post-nav">
          <div class="post-nav-next post-nav-item">
            
              <a href="/%E8%AE%A1%E7%AE%97%E6%9C%BA%E7%BD%91%E7%BB%9C/%E8%AE%A1%E7%AE%97%E6%9C%BA%E7%BD%91%E7%BB%9C-HTTP/" rel="next" title="计算机网络——http">
                <i class="fa fa-chevron-left"></i> 计算机网络——http
              </a>
            
          </div>

          <span class="post-nav-divider"></span>

          <div class="post-nav-prev post-nav-item">
            
              <a href="/%E6%95%B0%E6%8D%AE%E5%BA%93/%E6%95%B0%E6%8D%AE%E5%BA%93%E7%B3%BB%E7%BB%9F%E5%8E%9F%E7%90%86/" rel="prev" title="数据库——数据库原理">
                数据库——数据库原理 <i class="fa fa-chevron-right"></i>
              </a>
            
          </div>
        </div>
      

      
      
    </footer>
  </div>
  
  
  
  </article>



    <div class="post-spread">
      
    </div>
  </div>


          </div>
          


          

  
    <div class="comments" id="comments">
    </div>
  



        </div>
        
          
  
  <div class="sidebar-toggle">
    <div class="sidebar-toggle-line-wrap">
      <span class="sidebar-toggle-line sidebar-toggle-line-first"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-middle"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-last"></span>
    </div>
  </div>

  <aside id="sidebar" class="sidebar">
    
    <div class="sidebar-inner">

      

      
        <ul class="sidebar-nav motion-element">
          <li class="sidebar-nav-toc sidebar-nav-active" data-target="post-toc-wrap">
            文章目录
          </li>
          <li class="sidebar-nav-overview" data-target="site-overview-wrap">
            站点概览
          </li>
        </ul>
      

      <section class="site-overview-wrap sidebar-panel">
        <div class="site-overview">
          <div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person">
            
              <img class="site-author-image" itemprop="image"
                src="/images/title.gif"
                alt="Lei Zhou" />
            
              <p class="site-author-name" itemprop="name">Lei Zhou</p>
              <p class="site-description motion-element" itemprop="description">业精于勤，荒于嬉；行成于思，毁于随</p>
          </div>

          <nav class="site-state motion-element">

            
              <div class="site-state-item site-state-posts">
              
                <a href="/archives/">
              
                  <span class="site-state-item-count">134</span>
                  <span class="site-state-item-name">日志</span>
                </a>
              </div>
            

            
              
              
              <div class="site-state-item site-state-categories">
                <a href="/categories/index.html">
                  <span class="site-state-item-count">21</span>
                  <span class="site-state-item-name">分类</span>
                </a>
              </div>
            

            
              
              
              <div class="site-state-item site-state-tags">
                <a href="/tags/index.html">
                  <span class="site-state-item-count">69</span>
                  <span class="site-state-item-name">标签</span>
                </a>
              </div>
            

          </nav>

          
            <div class="feed-link motion-element">
              <a href="/atom.xml" rel="alternate">
                <i class="fa fa-rss"></i>
                RSS
              </a>
            </div>
          

          
            <div class="links-of-author motion-element">
                
                  <span class="links-of-author-item">
                    <a href="https://github.com/zlleige" target="_blank" title="GitHub">
                      
                        <i class="fa fa-fw fa-github"></i>GitHub</a>
                  </span>
                
                  <span class="links-of-author-item">
                    <a href="https://blog.csdn.net/zlleige" target="_blank" title="CSDN">
                      
                        <i class="fa fa-fw fa-crosshairs"></i>CSDN</a>
                  </span>
                
                  <span class="links-of-author-item">
                    <a href="https://www.cnblogs.com/zlleige/" target="_blank" title="博客园">
                      
                        <i class="fa fa-fw fa-rss-square"></i>博客园</a>
                  </span>
                
                  <span class="links-of-author-item">
                    <a href="https://www.zhihu.com/people/zlleige" target="_blank" title="知乎">
                      
                        <i class="fa fa-fw fa-paper-plane-o"></i>知乎</a>
                  </span>
                
            </div>
          

          
          
            <div class="cc-license motion-element" itemprop="license">
              <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/" class="cc-opacity" target="_blank">
                <img src="/images/cc-by-nc-sa.svg" alt="Creative Commons" />
              </a>
            </div>
          

          
          

          

        </div>
      </section>

      
      <!--noindex-->
        <section class="post-toc-wrap motion-element sidebar-panel sidebar-panel-active">
          <div class="post-toc">

            
              
            

            
              <div class="post-toc-content"><ol class="nav"><li class="nav-item nav-level-1"><a class="nav-link" href="#一、基础"><span class="nav-number">1.</span> <span class="nav-text">一、基础</span></a></li><li class="nav-item nav-level-1"><a class="nav-link" href="#二、创建表"><span class="nav-number">2.</span> <span class="nav-text">二、创建表</span></a></li><li class="nav-item nav-level-1"><a class="nav-link" href="#三、修改表"><span class="nav-number">3.</span> <span class="nav-text">三、修改表</span></a></li><li class="nav-item nav-level-1"><a class="nav-link" href="#四、插入"><span class="nav-number">4.</span> <span class="nav-text">四、插入</span></a></li><li class="nav-item nav-level-1"><a class="nav-link" href="#五、更新"><span class="nav-number">5.</span> <span class="nav-text">五、更新</span></a></li><li class="nav-item nav-level-1"><a class="nav-link" href="#六、删除"><span class="nav-number">6.</span> <span class="nav-text">六、删除</span></a></li><li class="nav-item nav-level-1"><a class="nav-link" href="#七、查询"><span class="nav-number">7.</span> <span class="nav-text">七、查询</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#DISTINCT"><span class="nav-number">7.1.</span> <span class="nav-text">DISTINCT</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#LIMIT"><span class="nav-number">7.2.</span> <span class="nav-text">LIMIT</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#八、排序"><span class="nav-number">8.</span> <span class="nav-text">八、排序</span></a></li><li class="nav-item nav-level-1"><a class="nav-link" href="#九、过滤"><span class="nav-number">9.</span> <span class="nav-text">九、过滤</span></a></li><li class="nav-item nav-level-1"><a class="nav-link" href="#十、通配符"><span class="nav-number">10.</span> <span class="nav-text">十、通配符</span></a></li><li class="nav-item nav-level-1"><a class="nav-link" href="#十一、计算字段"><span class="nav-number">11.</span> <span class="nav-text">十一、计算字段</span></a></li><li class="nav-item nav-level-1"><a class="nav-link" href="#十二、函数"><span class="nav-number">12.</span> <span class="nav-text">十二、函数</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#汇总"><span class="nav-number">12.1.</span> <span class="nav-text">汇总</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#文本处理"><span class="nav-number">12.2.</span> <span class="nav-text">文本处理</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#日期和时间处理"><span class="nav-number">12.3.</span> <span class="nav-text">日期和时间处理</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#数值处理"><span class="nav-number">12.4.</span> <span class="nav-text">数值处理</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#十三、分组"><span class="nav-number">13.</span> <span class="nav-text">十三、分组</span></a></li><li class="nav-item nav-level-1"><a class="nav-link" href="#十四、子查询"><span class="nav-number">14.</span> <span class="nav-text">十四、子查询</span></a></li><li class="nav-item nav-level-1"><a class="nav-link" href="#十五、连接"><span class="nav-number">15.</span> <span class="nav-text">十五、连接</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#内连接"><span class="nav-number">15.1.</span> <span class="nav-text">内连接</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#自连接"><span class="nav-number">15.2.</span> <span class="nav-text">自连接</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#自然连接"><span class="nav-number">15.3.</span> <span class="nav-text">自然连接</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#外连接"><span class="nav-number">15.4.</span> <span class="nav-text">外连接</span></a></li></ol></li><li class="nav-item nav-level-1"><a class="nav-link" href="#十六、组合查询"><span class="nav-number">16.</span> <span class="nav-text">十六、组合查询</span></a></li><li class="nav-item nav-level-1"><a class="nav-link" href="#十七、视图"><span class="nav-number">17.</span> <span class="nav-text">十七、视图</span></a></li><li class="nav-item nav-level-1"><a class="nav-link" href="#十八、存储过程"><span class="nav-number">18.</span> <span class="nav-text">十八、存储过程</span></a></li><li class="nav-item nav-level-1"><a class="nav-link" href="#十九、游标"><span class="nav-number">19.</span> <span class="nav-text">十九、游标</span></a></li><li class="nav-item nav-level-1"><a class="nav-link" href="#二十、触发器"><span class="nav-number">20.</span> <span class="nav-text">二十、触发器</span></a></li><li class="nav-item nav-level-1"><a class="nav-link" href="#二十一、事务管理"><span class="nav-number">21.</span> <span class="nav-text">二十一、事务管理</span></a></li><li class="nav-item nav-level-1"><a class="nav-link" href="#二十二、字符集"><span class="nav-number">22.</span> <span class="nav-text">二十二、字符集</span></a></li><li class="nav-item nav-level-1"><a class="nav-link" href="#二十三、权限管理"><span class="nav-number">23.</span> <span class="nav-text">二十三、权限管理</span></a></li><li class="nav-item nav-level-1"><a class="nav-link" href="#参考资料"><span class="nav-number">24.</span> <span class="nav-text">参考资料</span></a></li></ol></div>
            

          </div>
        </section>
      <!--/noindex-->
      

      
        <div class="back-to-top">
          <i class="fa fa-arrow-up"></i>
          
            <span id="scrollpercent"><span>0</span>%</span>
          
        </div>
      

    </div>
  </aside>


        
      </div>
    </main>

    <footer id="footer" class="footer">
      <div class="footer-inner">
        <script async src="https://busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>

<div class="copyright">&copy; 2019 &mdash; <span itemprop="copyrightYear">2023</span>
  <span class="with-love">
    <i class="fa fa-user"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">zlleige</span>

  
</div>

<!-- 

  <div class="powered-by">由 <a class="theme-link" target="_blank" href="https://hexo.io">Hexo</a> 强力驱动</div>





-->



<div class="powered-by">
<i class="fa fa-user-md"></i><span id="busuanzi_container_site_uv">
  本站访客数:<span id="busuanzi_value_site_uv"></span>
</span>
</div>

<div class="theme-info">
  <div class="powered-by"></div>
  <span class="post-count">博客全站共124.7k字</span>
</div>

        







        
      </div>
    </footer>

    

    

  </div>

  

<script type="text/javascript">
  if (Object.prototype.toString.call(window.Promise) !== '[object Function]') {
    window.Promise = null;
  }
</script>









  


  











  
  
    <script type="text/javascript" src="/lib/jquery/index.js?v=2.1.3"></script>
  

  
  
    <script type="text/javascript" src="/lib/fastclick/lib/fastclick.min.js?v=1.0.6"></script>
  

  
  
    <script type="text/javascript" src="/lib/jquery_lazyload/jquery.lazyload.js?v=1.9.7"></script>
  

  
  
    <script type="text/javascript" src="/lib/velocity/velocity.min.js?v=1.2.1"></script>
  

  
  
    <script type="text/javascript" src="/lib/velocity/velocity.ui.min.js?v=1.2.1"></script>
  

  
  
    <script type="text/javascript" src="/lib/fancybox/source/jquery.fancybox.pack.js?v=2.1.5"></script>
  

  
  
    <script type="text/javascript" src="/lib/canvas-nest/canvas-nest.min.js"></script>
  


  


  <script type="text/javascript" src="/js/src/utils.js?v=5.1.4"></script>

  <script type="text/javascript" src="/js/src/motion.js?v=5.1.4"></script>



  
  


  <script type="text/javascript" src="/js/src/affix.js?v=5.1.4"></script>

  <script type="text/javascript" src="/js/src/schemes/pisces.js?v=5.1.4"></script>



  
  <script type="text/javascript" src="/js/src/scrollspy.js?v=5.1.4"></script>
<script type="text/javascript" src="/js/src/post-details.js?v=5.1.4"></script>



  


  <script type="text/javascript" src="/js/src/bootstrap.js?v=5.1.4"></script>



  


  




	





  





  










  <script src="//cdn1.lncld.net/static/js/3.0.4/av-min.js"></script>
  <script src="//unpkg.com/valine/dist/Valine.min.js"></script>
  
  <script type="text/javascript">
    var GUEST = ['nick','mail','link'];
    var guest = 'nick,mail,link';
    guest = guest.split(',').filter(item=>{
      return GUEST.indexOf(item)>-1;
    });
    new Valine({
        el: '#comments' ,
        verify: false,
        notify: false,
        appId: 'upVVfHMDcdxMK5SBrji2hCTc-gzGzoHsz',
        appKey: 'zMXMLtk9W57v27fepiRocOrc',
        placeholder: 'Just go go',
        avatar:'mm',
        guest_info:guest,
        pageSize:'10' || 10,
    });
  </script>



  

  <script type="text/javascript">
    // Popup Window;
    var isfetched = false;
    var isXml = true;
    // Search DB path;
    var search_path = "./public/search.xml";
    if (search_path.length === 0) {
      search_path = "search.xml";
    } else if (/json$/i.test(search_path)) {
      isXml = false;
    }
    var path = "/" + search_path;
    // monitor main search box;

    var onPopupClose = function (e) {
      $('.popup').hide();
      $('#local-search-input').val('');
      $('.search-result-list').remove();
      $('#no-result').remove();
      $(".local-search-pop-overlay").remove();
      $('body').css('overflow', '');
    }

    function proceedsearch() {
      $("body")
        .append('<div class="search-popup-overlay local-search-pop-overlay"></div>')
        .css('overflow', 'hidden');
      $('.search-popup-overlay').click(onPopupClose);
      $('.popup').toggle();
      var $localSearchInput = $('#local-search-input');
      $localSearchInput.attr("autocapitalize", "none");
      $localSearchInput.attr("autocorrect", "off");
      $localSearchInput.focus();
    }

    // search function;
    var searchFunc = function(path, search_id, content_id) {
      'use strict';

      // start loading animation
      $("body")
        .append('<div class="search-popup-overlay local-search-pop-overlay">' +
          '<div id="search-loading-icon">' +
          '<i class="fa fa-spinner fa-pulse fa-5x fa-fw"></i>' +
          '</div>' +
          '</div>')
        .css('overflow', 'hidden');
      $("#search-loading-icon").css('margin', '20% auto 0 auto').css('text-align', 'center');

      $.ajax({
        url: path,
        dataType: isXml ? "xml" : "json",
        async: true,
        success: function(res) {
          // get the contents from search data
          isfetched = true;
          $('.popup').detach().appendTo('.header-inner');
          var datas = isXml ? $("entry", res).map(function() {
            return {
              title: $("title", this).text(),
              content: $("content",this).text(),
              url: $("url" , this).text()
            };
          }).get() : res;
          var input = document.getElementById(search_id);
          var resultContent = document.getElementById(content_id);
          var inputEventFunction = function() {
            var searchText = input.value.trim().toLowerCase();
            var keywords = searchText.split(/[\s\-]+/);
            if (keywords.length > 1) {
              keywords.push(searchText);
            }
            var resultItems = [];
            if (searchText.length > 0) {
              // perform local searching
              datas.forEach(function(data) {
                var isMatch = false;
                var hitCount = 0;
                var searchTextCount = 0;
                var title = data.title.trim();
                var titleInLowerCase = title.toLowerCase();
                var content = data.content.trim().replace(/<[^>]+>/g,"");
                var contentInLowerCase = content.toLowerCase();
                var articleUrl = decodeURIComponent(data.url);
                var indexOfTitle = [];
                var indexOfContent = [];
                // only match articles with not empty titles
                if(title != '') {
                  keywords.forEach(function(keyword) {
                    function getIndexByWord(word, text, caseSensitive) {
                      var wordLen = word.length;
                      if (wordLen === 0) {
                        return [];
                      }
                      var startPosition = 0, position = [], index = [];
                      if (!caseSensitive) {
                        text = text.toLowerCase();
                        word = word.toLowerCase();
                      }
                      while ((position = text.indexOf(word, startPosition)) > -1) {
                        index.push({position: position, word: word});
                        startPosition = position + wordLen;
                      }
                      return index;
                    }

                    indexOfTitle = indexOfTitle.concat(getIndexByWord(keyword, titleInLowerCase, false));
                    indexOfContent = indexOfContent.concat(getIndexByWord(keyword, contentInLowerCase, false));
                  });
                  if (indexOfTitle.length > 0 || indexOfContent.length > 0) {
                    isMatch = true;
                    hitCount = indexOfTitle.length + indexOfContent.length;
                  }
                }

                // show search results

                if (isMatch) {
                  // sort index by position of keyword

                  [indexOfTitle, indexOfContent].forEach(function (index) {
                    index.sort(function (itemLeft, itemRight) {
                      if (itemRight.position !== itemLeft.position) {
                        return itemRight.position - itemLeft.position;
                      } else {
                        return itemLeft.word.length - itemRight.word.length;
                      }
                    });
                  });

                  // merge hits into slices

                  function mergeIntoSlice(text, start, end, index) {
                    var item = index[index.length - 1];
                    var position = item.position;
                    var word = item.word;
                    var hits = [];
                    var searchTextCountInSlice = 0;
                    while (position + word.length <= end && index.length != 0) {
                      if (word === searchText) {
                        searchTextCountInSlice++;
                      }
                      hits.push({position: position, length: word.length});
                      var wordEnd = position + word.length;

                      // move to next position of hit

                      index.pop();
                      while (index.length != 0) {
                        item = index[index.length - 1];
                        position = item.position;
                        word = item.word;
                        if (wordEnd > position) {
                          index.pop();
                        } else {
                          break;
                        }
                      }
                    }
                    searchTextCount += searchTextCountInSlice;
                    return {
                      hits: hits,
                      start: start,
                      end: end,
                      searchTextCount: searchTextCountInSlice
                    };
                  }

                  var slicesOfTitle = [];
                  if (indexOfTitle.length != 0) {
                    slicesOfTitle.push(mergeIntoSlice(title, 0, title.length, indexOfTitle));
                  }

                  var slicesOfContent = [];
                  while (indexOfContent.length != 0) {
                    var item = indexOfContent[indexOfContent.length - 1];
                    var position = item.position;
                    var word = item.word;
                    // cut out 100 characters
                    var start = position - 20;
                    var end = position + 80;
                    if(start < 0){
                      start = 0;
                    }
                    if (end < position + word.length) {
                      end = position + word.length;
                    }
                    if(end > content.length){
                      end = content.length;
                    }
                    slicesOfContent.push(mergeIntoSlice(content, start, end, indexOfContent));
                  }

                  // sort slices in content by search text's count and hits' count

                  slicesOfContent.sort(function (sliceLeft, sliceRight) {
                    if (sliceLeft.searchTextCount !== sliceRight.searchTextCount) {
                      return sliceRight.searchTextCount - sliceLeft.searchTextCount;
                    } else if (sliceLeft.hits.length !== sliceRight.hits.length) {
                      return sliceRight.hits.length - sliceLeft.hits.length;
                    } else {
                      return sliceLeft.start - sliceRight.start;
                    }
                  });

                  // select top N slices in content

                  var upperBound = parseInt('1');
                  if (upperBound >= 0) {
                    slicesOfContent = slicesOfContent.slice(0, upperBound);
                  }

                  // highlight title and content

                  function highlightKeyword(text, slice) {
                    var result = '';
                    var prevEnd = slice.start;
                    slice.hits.forEach(function (hit) {
                      result += text.substring(prevEnd, hit.position);
                      var end = hit.position + hit.length;
                      result += '<b class="search-keyword">' + text.substring(hit.position, end) + '</b>';
                      prevEnd = end;
                    });
                    result += text.substring(prevEnd, slice.end);
                    return result;
                  }

                  var resultItem = '';

                  if (slicesOfTitle.length != 0) {
                    resultItem += "<li><a href='" + articleUrl + "' class='search-result-title'>" + highlightKeyword(title, slicesOfTitle[0]) + "</a>";
                  } else {
                    resultItem += "<li><a href='" + articleUrl + "' class='search-result-title'>" + title + "</a>";
                  }

                  slicesOfContent.forEach(function (slice) {
                    resultItem += "<a href='" + articleUrl + "'>" +
                      "<p class=\"search-result\">" + highlightKeyword(content, slice) +
                      "...</p>" + "</a>";
                  });

                  resultItem += "</li>";
                  resultItems.push({
                    item: resultItem,
                    searchTextCount: searchTextCount,
                    hitCount: hitCount,
                    id: resultItems.length
                  });
                }
              })
            };
            if (keywords.length === 1 && keywords[0] === "") {
              resultContent.innerHTML = '<div id="no-result"><i class="fa fa-search fa-5x" /></div>'
            } else if (resultItems.length === 0) {
              resultContent.innerHTML = '<div id="no-result"><i class="fa fa-frown-o fa-5x" /></div>'
            } else {
              resultItems.sort(function (resultLeft, resultRight) {
                if (resultLeft.searchTextCount !== resultRight.searchTextCount) {
                  return resultRight.searchTextCount - resultLeft.searchTextCount;
                } else if (resultLeft.hitCount !== resultRight.hitCount) {
                  return resultRight.hitCount - resultLeft.hitCount;
                } else {
                  return resultRight.id - resultLeft.id;
                }
              });
              var searchResultList = '<ul class=\"search-result-list\">';
              resultItems.forEach(function (result) {
                searchResultList += result.item;
              })
              searchResultList += "</ul>";
              resultContent.innerHTML = searchResultList;
            }
          }

          if ('auto' === 'auto') {
            input.addEventListener('input', inputEventFunction);
          } else {
            $('.search-icon').click(inputEventFunction);
            input.addEventListener('keypress', function (event) {
              if (event.keyCode === 13) {
                inputEventFunction();
              }
            });
          }

          // remove loading animation
          $(".local-search-pop-overlay").remove();
          $('body').css('overflow', '');

          proceedsearch();
        }
      });
    }

    // handle and trigger popup window;
    $('.popup-trigger').click(function(e) {
      e.stopPropagation();
      if (isfetched === false) {
        searchFunc(path, 'local-search-input', 'local-search-result');
      } else {
        proceedsearch();
      };
    });

    $('.popup-btn-close').click(onPopupClose);
    $('.popup').click(function(e){
      e.stopPropagation();
    });
    $(document).on('keyup', function (event) {
      var shouldDismissSearchPopup = event.which === 27 &&
        $('.search-popup').is(':visible');
      if (shouldDismissSearchPopup) {
        onPopupClose();
      }
    });
  </script>





  

  

  

  
  

  

  

  

  
  <style>
    .copy-btn {
      display: inline-block;
      padding: 6px 12px;
      font-size: 13px;
      font-weight: 700;
      line-height: 20px;
      color: #333;
      white-space: nowrap;
      vertical-align: middle;
      cursor: pointer;
      background-color: #eee;
      background-image: linear-gradient(#fcfcfc, #eee);
      border: 1px solid #d5d5d5;
      border-radius: 3px;
      user-select: none;
      outline: 0;
    }

    .highlight-wrap .copy-btn {
      transition: opacity .3s ease-in-out;
      opacity: 0;
      padding: 2px 6px;
      position: absolute;
      right: 4px;
      top: 8px;
    }

    .highlight-wrap:hover .copy-btn,
    .highlight-wrap .copy-btn:focus {
      opacity: 1
    }

    .highlight-wrap {
      position: relative;
    }
  </style>
  
  <script>
    $('.highlight').each(function (i, e) {
      var $wrap = $('<div>').addClass('highlight-wrap')
      $(e).after($wrap)
      $wrap.append($('<button>').addClass('copy-btn').append('复制').on('click', function (e) {
        var code = $(this).parent().find('.code').find('.line').map(function (i, e) {
          return $(e).text()
        }).toArray().join('\n')
        var ta = document.createElement('textarea')
        document.body.appendChild(ta)
        ta.style.position = 'absolute'
        ta.style.top = '0px'
        ta.style.left = '0px'
        ta.value = code
        ta.select()
        ta.focus()
        var result = document.execCommand('copy')
        document.body.removeChild(ta)
        
          if(result)$(this).text('复制成功')
          else $(this).text('复制失败')
        
        $(this).blur()
      })).on('mouseleave', function (e) {
        var $b = $(this).find('.copy-btn')
        setTimeout(function () {
          $b.text('复制')
        }, 300)
      }).append(e)
    })
  </script>


  
<script src="/live2dw/lib/L2Dwidget.min.js?094cbace49a39548bed64abff5988b05"></script><script>L2Dwidget.init({"pluginRootPath":"live2dw/","pluginJsPath":"lib/","pluginModelPath":"assets/","tagMode":false,"model":{"jsonPath":"/live2dw/assets/wanko.model.json"},"display":{"position":"right","width":150,"height":300},"mobile":{"show":false},"log":false});</script></body>
</html>
<!-- 页面点击小红心 -->
<script type="text/javascript" src="/js/src/clicklove.js"></script>

